#!/usr/bin/perl -w

use warnings;
use strict;

use Switch;
use DBI;

my $ignoreteams = "	userid != 2055219
		and	userid != 21701
		and	userid != 38396
		and	userid != 547054
		and	userid != 37023
		and	userid != 86189
		and	userid != 43945
		and	userid != 43625
		and	userid != 70695
		and	userid != 2792893
		and	userid != 2808084
		and	userid != 2797411
		and	userid != 4310548
		and	userid != 4310548
		and	userid != 3955802
		and	userid != 5175981
		and	userid != 4287098
		and	userid != 2797433
		and	userid != 27698
		and	userid != 27808
		and	userid != 27827
		and	userid != 27891";

my $graphonly = 0;

my $dbh = DBI->connect("DBI:mysql:maptrick:HOSTNAME","USERNAME",'PASSWORD');

my $firstsignup = "2000-12-02 00:00:00";
my $cutoff = "2007-06-01 00:00:00"; #never less than $firstsignup

my $seasons = 33;

my $sth;

$sth = $dbh->prepare("SELECT leagueid, min( unix_timestamp(signupdate) ) su
FROM `teams_der`
WHERE userid >0
AND signupdate > '$firstsignup'
GROUP BY leagueid");

$sth->execute;

my @start = ();
my @end = ();

while (my @result = $sth->fetchrow()) {
	$start[$result[0]] = $result[1];
}

for (my $i = 1; $i <= 200; $i++) {
	$end[$i] = 0;
}

my %data;

$sth = $dbh->prepare("SELECT leagueid, unix_timestamp(signupdate) signup, ( unix_timestamp( activationdate ) - unix_timestamp( signupdate ) ) / 86400 wait from `teams_der` where userid > 0 and signupdate > '$cutoff' AND $ignoreteams ORDER BY signupdate ASC");

$sth->execute;

my $totalteams = 0;
while (my @result = $sth->fetchrow()) {
	$data{$result[0]}{$result[1]} = $result[2];
	$end[$result[0]] = $result[1] if $result[1] != $end[$result[0]];
	$totalteams++;
}

my $rrdtoolcmd = "/scratch/lhoek/rrdtool/bin/rrdtool";
my $rrdpath = "/scratch/lhoek/rrds";
my $graphpath = "/home/lhoek/public_html/graphs";
my $plot = "AREA:time#FF0000:\"Time between application and activation (days)\"";
my $comment = "COMMENT:\"\\s\" COMMENT:\"\\s\" COMMENT:\"\\s\" COMMENT:\"Visit http\\://websec.science.uva.nl/~lhoek/allgraphs.php for more graphs!\\c\"";


$sth = $dbh->prepare("SELECT leagueid, englishname FROM leagues");

$sth->execute;

my %name;

while (my @result = $sth->fetchrow()) {
	$name{$result[0]} = $result[1];
}

$sth = $dbh->prepare("SELECT * FROM seasons");

$sth->execute;

my %expansions;

while (my @result = $sth->fetchrow()) {
	for (my $i = 1; $i <= $seasons; $i++) {
		$expansions{$result[0]}{$result[$i]} = $i if !defined $expansions{$result[0]}{$result[$i]};
	}
}

my $c = 0;

foreach my $country (sort {$a <=> $b} (keys %data)) {

#	next if ($country <= 122);
#	next if ($country != 30);

	my $graphopts = "-w 500 -h 200 -X 0 -A -a PNG -s $start[$country] -e $end[$country]";
	my $biggraphopts = "-w 900 -h 500 -X 0 -A -a PNG -s $start[$country] -e $end[$country]";
#	doesn't work on Solaris
#	my $season11 = `date +"%s" -d 2000-10-22`;
	my $season11 = 972165600;
	my $vrule = "";

	for (my $i = 1; $i <= 12; $i++) {
		my $color = "";
		if (defined($expansions{$country}{$i}) and $expansions{$country}{$i} != 29) {
			switch ($i) {
				case 1	{$color = "#C090FF"}
				case 2	{$color = "#90C0FF"}
				case 3	{$color = "#00FFFF"}
				case 4	{$color = "#00FF00"}
				case 5	{$color = "#FFFF00"}
				case 6	{$color = "#FFCC00"}
				case 7	{$color = "#FF9900"}
				case 8	{$color = "#FF0000"}
				case 9	{$color = "#800080"}
				case 10	{$color = "#FF43CC"}
				case 11 {$color = "#000000"}
				case 12 {$color = "#0000FF"}
				else	{die "undefined size!"}
			}
			my $newseason = $season11 + (($expansions{$country}{$i}-11)*16*7*24*60*60);
			$vrule = $vrule . "VRULE:$newseason$color:\"Added div. $i\" ";
		}
	}
	my $newseason = $season11 + 18*16*7*24*60*60;
	$vrule = $vrule . "VRULE:$newseason#808080:\"League reform\" ";

	#my $season11 = `date +"%s" -d 2000-10-22`;
	#for (my $i = 11; $i <= 25; $i++) {
	#	my $newseason = $season11 + (($i-11)*16*7*24*60*60);
	#	my $legend = "";
	#	$legend = ":\"Start of new season\"" if $i == 25;
	#	$vrule = $vrule . "VRULE:$newseason#0000FF$legend ";
	#}

	foreach my $user (sort {$a <=> $b} (keys %{ $data{$country} } )) {
		next if $graphonly > 0;
		my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($user);
		$c++;
		$year+=1900; $mon++;
		print "$country: $mday-$mon-$year $hour:$min:$sec\t->\t$data{$country}{$user} days ($c/$totalteams)\n";
		`$rrdtoolcmd update $rrdpath/$country-t.rrd $user:$data{$country}{$user}`;
	}
	`$rrdtoolcmd graph $graphpath/$country-t.png --title="Time spent on waiting list ($name{$country})" $graphopts DEF:time=$rrdpath/$country-t.rrd:time:AVERAGE $plot $vrule $comment`;
	`$rrdtoolcmd graph $graphpath/big$country-t.png --title="Time spent on waiting list ($name{$country})" $biggraphopts DEF:time=$rrdpath/$country-t.rrd:time:AVERAGE $plot $vrule $comment`;
}

$dbh->disconnect;
exit;
